package com.google.srclib.io.cvs;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;

import org.h2.constant.SysProperties;
import org.h2.message.Message;
import org.h2.tools.SimpleResultSet;
import org.h2.util.JdbcUtils;


/**
 * 
 * @author YLiang
 * 
 */
public class CSVExporter {
	private final static String fieldSeparatorWrite = ",";
	private static String rowSeparatorWrite;
	private static char fieldDelimiter = '\"';
	private static char escapeCharacter = '\"';
	private static String lineSeparator = SysProperties.LINE_SEPARATOR;
	private static String nullString = "";
	private static CSVExporter singleton = null;

	public static CSVExporter getInstance() {
		if (singleton == null) {
			singleton = new CSVExporter();
		}
		return singleton;
	}

	/**
	 * @param out
	 * @param rs
	 * @return rows number
	 * @throws SQLException
	 */
	public int write(OutputStream out, ResultSet rs) throws SQLException {

		try {
			ResultSetMetaData meta = rs.getMetaData();
			int rows = 0;
			int columnCount = meta.getColumnCount();
			String[] row = new String[columnCount];
			for (int i = 0; i < columnCount; i++) {
				row[i] = meta.getColumnLabel(i + 1);
			}
			writeRow(row, out);
			while (rs.next()) {
				for (int i = 0; i < columnCount; i++) {
					row[i] = rs.getString(i + 1);
				}
				writeRow(row, out);
				rows++;
			}
			return rows;
		} catch (IOException e) {
			throw Message.convertIOException(e, null);
		} finally {
			JdbcUtils.closeSilently(rs);
		}
	}

	/**
	 * @param values
	 * @param out
	 * @throws IOException
	 */
	private void writeRow(String[] values, OutputStream out) throws IOException {
		byte[] buf = new byte[1024];
		for (int i = 0; i < values.length; i++) {
			if (i > 0) {
				if (fieldSeparatorWrite != null) {
					buf = fieldSeparatorWrite.getBytes();
					out.write(buf, 0, buf.length);
				}
			}
			String s = values[i];
			if (s != null) {
				if (escapeCharacter != 0) {
					if (fieldDelimiter != 0) {
						out.write(fieldDelimiter);
					}
					out.write(escape(s).getBytes());
					if (fieldDelimiter != 0) {
						out.write(fieldDelimiter);
					}
				} else {
					buf = s.getBytes();
					out.write(buf, 0, buf.length);
				}
			} else if (nullString != null && nullString.length() > 0) {
				buf = nullString.getBytes();
				out.write(buf, 0, buf.length);
			}
		}
		if (rowSeparatorWrite != null) {
			buf = rowSeparatorWrite.getBytes();
			out.write(buf, 0, buf.length);
		}
		buf = lineSeparator.getBytes();
		out.write(buf, 0, buf.length);
	}

	/**
	 * @param data
	 * @return
	 */
	private String escape(String data) {
		if (data.indexOf(fieldDelimiter) < 0) {
			if (escapeCharacter == fieldDelimiter
					|| data.indexOf(escapeCharacter) < 0) {
				return data;
			}
		}
		StringBuilder buff = new StringBuilder(data.length());
		for (int i = 0; i < data.length(); i++) {
			char ch = data.charAt(i);
			if (ch == fieldDelimiter || ch == escapeCharacter) {
				buff.append(escapeCharacter);
			}
			buff.append(ch);
		}
		return buff.toString();
	}

	private SimpleResultSet getSimpleResultSet() throws SQLException {
		SimpleResultSet rs = new SimpleResultSet();
		rs.addColumn("NAME", Types.VARCHAR, 255, 0);
		rs.addColumn("EMAIL", Types.VARCHAR, 255, 0);
		rs.addRow(new Object[] { "Bob Meier", "bob.meier@abcde.abc" });
		rs.addRow(new Object[] { "John Jones", "john.jones@abcde.abc" });
		rs.addRow(new Object[] { "Sam Diego", "sam.diego@abcde.abc" });
		return rs;
	}

	public static void main(String[] args) throws SQLException, IOException {
		CSVExporter exporter = CSVExporter.getInstance();
		FileOutputStream os = new FileOutputStream("test.csv");
		exporter.write(os, exporter.getSimpleResultSet());
	}

}
